﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {
        public static List<TinyItem> GetAllUnits(int LanguageCode)
        {
            _Command.CommandText = "SP_GetCategoryItems";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();


            _Command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 11;
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            _DataSet.Tables.Clear();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            { }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<TinyItem> _items = new List<TinyItem>();

                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    TinyItem _tempItem = new TinyItem(Convert.ToInt32(dr["ItemId"]), Convert.ToString(dr["ItemName"]));
                    _items.Add(_tempItem);
                }
                //TinyItem _emptyItem = new TinyItem(0, "");
                //_items.Insert(0, _emptyItem);

                return _items;
            }
            else
                return null;
        }

        public static List<TinyItem> GetAllStrategicObjectiveItems(int LanguageCode)
        {
            _Command.CommandText = "SP_GetCategoryItems";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();


            _Command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 12;
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            _DataSet.Tables.Clear();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            { }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<TinyItem> _items = new List<TinyItem>();

                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    TinyItem _tempItem = new TinyItem(Convert.ToInt32(dr["ItemId"]), Convert.ToString(dr["ItemName"]));
                    _items.Add(_tempItem);
                }

                return _items;
            }
            else
                return null;
        }

        public static StrategicObjectives GetOneStrategicObjectiveItemValuesById(int ItemId, int CountryId)
        {
            _Command.CommandText = "SP_GetStrategicObjectiveItemValues";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            // Add Item Id Param
            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            // Add Country Id Param
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            StrategicObjectives _StrategicObjective = new StrategicObjectives();
            _StrategicObjective.ItemId = ItemId;
            _StrategicObjective.CountryId = CountryId;

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {

                _StrategicObjective.UnitId = Convert.ToInt32(_DataSet.Tables[0].Rows[0]["UnitId"]);

                if (_DataSet.Tables[0].Rows[0]["IsEE"] == DBNull.Value)
                    _StrategicObjective.IsEE = false;
                else
                    _StrategicObjective.IsEE = Convert.ToBoolean(_DataSet.Tables[0].Rows[0]["IsEE"]);

                if (_DataSet.Tables[0].Rows[0]["IsRE"] == DBNull.Value)
                    _StrategicObjective.IsRE = false;
                else
                    _StrategicObjective.IsRE = Convert.ToBoolean(_DataSet.Tables[0].Rows[0]["IsRE"]);


                for (int columnIndex = 0; columnIndex < _DataSet.Tables[0].Columns.Count - 5; columnIndex++)
                {

                    StrategicObjectivesItemValue tempItemValue = new StrategicObjectivesItemValue();
                    tempItemValue.Year = Convert.ToInt32(_DataSet.Tables[0].Columns[columnIndex].ColumnName);
                    if (_DataSet.Tables[0].Rows[0][columnIndex] != DBNull.Value)
                        tempItemValue.Value = Convert.ToSingle(_DataSet.Tables[0].Rows[0][columnIndex]);
                    else
                        tempItemValue.Value = -1;
                    _StrategicObjective.ItemValues.Add(tempItemValue);
                }
                _StrategicObjective.ItemValues.Sort(delegate(StrategicObjectivesItemValue _item1, StrategicObjectivesItemValue _item2) { return _item1.Year.CompareTo(_item2.Year); });
            }
            else
            {

                _StrategicObjective.UnitId = 0;

                _StrategicObjective.IsEE = false;

                _StrategicObjective.IsRE = false;


                for (int columnIndex = 0; columnIndex < _DataSet.Tables[0].Columns.Count - 5; columnIndex++)
                {
                    StrategicObjectivesItemValue tempItemValue = new StrategicObjectivesItemValue();
                    tempItemValue.Year = Convert.ToInt32(_DataSet.Tables[0].Columns[columnIndex].ColumnName);
                    tempItemValue.Value = -1;
                    _StrategicObjective.ItemValues.Add(tempItemValue);
                }
                _StrategicObjective.ItemValues.Sort(delegate(StrategicObjectivesItemValue _item1, StrategicObjectivesItemValue _item2) { return _item1.Year.CompareTo(_item2.Year); });
            }
            return _StrategicObjective;
        }

        public static bool UpdateStrategicObjectiveItemValues(StrategicObjectives _strategicObjective)
        {

            #region Command Text

            _Command.CommandText = @" BEGIN TRANSACTION
                                      DELETE FROM StrategicObjectives WHERE ItemId= @ItemId AND CountryId = @CountryId;
                                      INSERT INTO StrategicObjectives ";

            string ColumnNames = "ItemId,CountryId,UnitId,IsEE,IsRE,";
            string Values = "@ItemId,@CountryId,@UnitId,@IsEE,@IsRE,";

            foreach (StrategicObjectivesItemValue _item in _strategicObjective.ItemValues)
            {
                ColumnNames += "[" + _item.Year.ToString() + "],";
                Values += _item.Value == -1 ? "NULL," : "'" + _item.Value.ToString() + "',";
            }
            ColumnNames = ColumnNames.Remove(ColumnNames.Length - 1);
            Values = Values.Remove(Values.Length - 1);

            _Command.CommandText += "(" + ColumnNames + ") VALUES (" + Values + ")";

            _Command.CommandText += @" IF @@ERROR <> 0
                                            ROLLBACK
                                        COMMIT";

            _Command.CommandType = CommandType.Text;

            #endregion

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = _strategicObjective.ItemId;
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = _strategicObjective.CountryId;

            if (_strategicObjective.UnitId != 0)
                _Command.Parameters.Add("@UnitId", SqlDbType.Int).Value = _strategicObjective.UnitId;
            else
                _Command.Parameters.Add("@UnitId", SqlDbType.Int).Value = DBNull.Value;

            _Command.Parameters.Add("@IsEE", SqlDbType.Bit).Value = _strategicObjective.IsEE;
            _Command.Parameters.Add("@IsRE", SqlDbType.Bit).Value = _strategicObjective.IsRE;

            #endregion

            try
            {
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }
        }

        public static DataSet GetAllStrategicObjectivesItemValues(int LanguageCode, int CountryId)
        {
            _Command.CommandText = "SP_GetAllStrategicObjectivesItemValues";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
            _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _DataSet = new DataSet();

            _Adapter.SelectCommand = _Command;
            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exc)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
                return null;
            }

            if (_DataSet != null && (_DataSet.Tables[0].Rows.Count > 0 || _DataSet.Tables[1].Rows.Count > 0))
                return _DataSet;
            else
                return null;

        }
    }
}
